package com.course.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DBConnection {
    private static final String URL = "jdbc:mysql://localhost:3306/course_management?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";
    
    private static final int INITIAL_POOL_SIZE = 5;
    private static final int MAX_POOL_SIZE = 10;
    private static List<Connection> connectionPool;
    private static List<Connection> usedConnections = new ArrayList<>();
    
    static {
        try {
            System.out.println("DBConnection: Attempting to load JDBC driver...");
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("DBConnection: JDBC driver loaded successfully.");
            initializePool();
        } catch (ClassNotFoundException e) {
            System.err.println("DBConnection: Failed to load JDBC driver!");
            e.printStackTrace();
        }
    }
    
    private static void initializePool() {
        connectionPool = new ArrayList<>(INITIAL_POOL_SIZE);
        for (int i = 0; i < INITIAL_POOL_SIZE; i++) {
            try {
                connectionPool.add(createConnection());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    private static Connection createConnection() throws SQLException {
        System.out.println("DBConnection: Creating new database connection...");
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }
    
    public static synchronized Connection getConnection() throws SQLException {
        // Try to get a valid connection from the pool
        while (!connectionPool.isEmpty()) {
            Connection connection = connectionPool.remove(connectionPool.size() - 1);
            if (connection.isValid(1)) { // Check if connection is valid
                usedConnections.add(connection);
                return connection;
            } else {
                try {
                    connection.close(); // Close invalid connection
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        // If pool is empty or all connections in pool were invalid, try to create a new one
        if (usedConnections.size() < MAX_POOL_SIZE) {
            Connection newConnection = createConnection();
            usedConnections.add(newConnection);
            return newConnection;
        } else {
            throw new SQLException("No available connections in the pool");
        }
    }
    
    public static synchronized void releaseConnection(Connection connection) {
        if (connection != null) {
            usedConnections.remove(connection);
            connectionPool.add(connection);
        }
    }
    
    public static void closeAllConnections() {
        for (Connection connection : usedConnections) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        for (Connection connection : connectionPool) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        usedConnections.clear();
        connectionPool.clear();
        System.out.println("DBConnection: All connections closed");
    }

} 